import pymysql
from dbutils.pooled_db import PooledDB
"""start所有的数据库连接"""
keyboard_conn_config =  {
        'host': 'rm-bp1keu78ml2tyo59eo.mysql.rds.aliyuncs.com',
        'user': 'keyboard',
        'password': 'Aa112211',
        'db': 'ylhy_keyboard',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor,
        'client_flag':pymysql.constants.CLIENT.MULTI_STATEMENTS
    }
# 创建连接池
keyboard_conn_pool = PooledDB(
    creator=pymysql,  # 使用pymysql作为数据库接口
    mincached=1,      # 初始化时创建的空闲连接数
    maxcached=5,      # 连接池中最大的空闲连接数
    maxconnections=10, # 连接池允许的最大连接数
    blocking=True,    # 连接池中没有可用连接时是否等待
    **keyboard_conn_config         # 其他数据库连接参数
)

def get_keyboard_conn():
    return keyboard_conn_pool.connection()
    # return pymysql.connect(**keyboard_conn_config)
pass

adm_conn_config =  {
        'host': 'rm-bp1keu78ml2tyo59eo.mysql.rds.aliyuncs.com',
        'user': 'keyboard',
        'password': 'Aa112211',
        'db': 'yl_device_msg',
        'charset': 'utf8',
        'cursorclass': pymysql.cursors.DictCursor,
        'client_flag':pymysql.constants.CLIENT.MULTI_STATEMENTS
    }

# 创建连接池
adm_conn_pool = PooledDB(
    creator=pymysql,  # 使用pymysql作为数据库接口
    mincached=1,      # 初始化时创建的空闲连接数
    maxcached=5,      # 连接池中最大的空闲连接数
    maxconnections=10, # 连接池允许的最大连接数
    blocking=True,    # 连接池中没有可用连接时是否等待
    **adm_conn_config         # 其他数据库连接参数
)
def get_adm_conn():
    return adm_conn_pool.connection()
    # return pymysql.connect(**adm_conn_config)
pass


recharge_conn_config =  {
        'host': 'rm-bp165j8y05vf43d47qo.mysql.rds.aliyuncs.com',
        'user': 'recharge',
        'password': 'Aa112211',
        'db': 'ylhy_recharge',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor,
        'client_flag':pymysql.constants.CLIENT.MULTI_STATEMENTS
    }

# 创建连接池
recharge_conn_pool = PooledDB(
    creator=pymysql,  # 使用pymysql作为数据库接口
    mincached=1,      # 初始化时创建的空闲连接数
    maxcached=5,      # 连接池中最大的空闲连接数
    maxconnections=10, # 连接池允许的最大连接数
    blocking=True,    # 连接池中没有可用连接时是否等待
    **recharge_conn_config         # 其他数据库连接参数
)
def get_recharge_conn():
    return recharge_conn_pool.connection()
    # return pymysql.connect(**recharge_conn_config)
pass




"""end所有的数据库连接"""

def SQL_ADD_FIELD_VALUES(locals_filed={},exclusion_field=[]):
    sql_field = []
    sql_values = []
    for it in locals_filed.keys():
        if it == 'self':
            continue;
        if it in exclusion_field:
            continue;
        if locals_filed[it] is not None:
            sql_field.append("`{0}`".format(it))
            sql_values.append("'{0}'".format(locals_filed[it]))
    sql_field = ','.join(sql_field)
    sql_values = ','.join(sql_values)
    return sql_field,sql_values

def SQL_UPDATE_SET(locals_filed={},exclusion_field=[]):
    sql_set = []
    for it in locals_filed.keys():
        if it == 'self':
            continue;
        if it in exclusion_field:
            continue;
        if locals_filed[it] is not None:
            sql_set.append("`{0}`='{1}'".format(it,locals_filed[it]))
    sql_set = ','.join(sql_set)
    return sql_set

def SQL_WHERE(locals_filed={},exclusion_field=[],only_contain_field=[]):
    sql_where = []
    for it in locals_filed.keys():
        if it == 'self':
            continue;
        if it in exclusion_field:
            continue;
        if len(only_contain_field)>0:
            if it in only_contain_field:
                if locals_filed[it] is not None:
                    sql_where.append(" `{0}`='{1}' ".format(it, locals_filed[it]))
        else:
            if locals_filed[it] is not None:
                # print(it)
                sql_where.append(" `{0}`='{1}' ".format(it, locals_filed[it]))

    sql_where = '  and '.join(sql_where)
    return sql_where

def SQL_LIKE(locals_filed={},exclusion_field=[],only_contain_field=[]):
    sql_where = []
    for it in locals_filed.keys():
        if it == 'self':
            continue;
        if it in exclusion_field:
            continue;
        if len(only_contain_field)>0:
            if it in only_contain_field:
                if locals_filed[it] is not None:
                    sql_where.append(" `{0}` like '%{1}%' ".format(it, locals_filed[it]))
        else:
            if locals_filed[it] is not None:
                sql_where.append(" `{0}` like '%{1}%' ".format(it, locals_filed[it]))
    sql_where = '  and '.join(sql_where)
    return sql_where

def SQL_ORDER(page_index=1,page_size=20,filed='id',sort='ascending'):
    # descending/ascending
    sql_order = """
            ORDER BY {0} {1}
    	    LIMIT {2},{3}
            """
    if sort=='ascending':
        sort="asc"
    else:
        sort='desc'
    limit = (int(page_index)-1) *int(page_size)
    filed = '`{0}`'.format(filed)
    sql_order = sql_order.format(filed,sort,limit,page_size)
    return sql_order











